#!/usr/bin/env python
#-*-encoding:utf-8-*-
'''
Created on 2014年12月13日

@author: chenyongbing
'''
import sys,os
current_dir = os.path.dirname(__file__)
from localConfig import baseLocalConfig








from PyMySQL import ConnectMySQL



retail_host = baseLocalConfig.mysqlConfig['retail']['host']
retail_port = baseLocalConfig.mysqlConfig['retail']['port']
retail_user = baseLocalConfig.mysqlConfig['retail']['user']
retail_passwd = baseLocalConfig.mysqlConfig['retail']['passwd']
retail_db = baseLocalConfig.mysqlConfig['retail']['db']
myRetail = ConnectMySQL(host=retail_host, user=retail_user, password=retail_passwd, db=retail_db)

import logging



class RetailDatas(object):
    '''
    classdocs
    '''


    def __init__(self):
        '''
        Constructor
        '''
        self.myRetail = myRetail
        
        
    def conv_tuple_to_str(self,alist=[]):
        if len(alist)==0:
            logging.error('List Is Null.')
            return ''
        
        if len(alist)==1:
            return '"%s"'%alist[0]
        blist = []
        for a in alist:
            blist.append(str(a))
        return '"'+'","'.join(blist)+'"'
    
    def get_cate_id(self,tab='cates',cate_name='',type='first'):
        query = 'select id from %s where cate_name="%s"'%(tab,cate_name)
        ret = myRetail.SelectOne(query)
        if ret:
            id = ret[0]
            return id
        return None
    
    
    def get_comp_and_groups(self,comp_id=None,comp_group=None):
        datas = {}
        query = 'select c.name,c.id,g.id,g.name from companies c,groups g where c.id=g.company_id and g.type=4'
        if comp_id ==None and comp_group==None:
            comp = comp_id
        elif comp_id==None:
            comp = self.get_comp_id(comp_group=comp_group)
            if comp==None:return datas
        else:
            comp = comp_id
        if comp!=None:
            query = query + ' and c.id="%s"'%comp
        rets = myRetail.SelectAll(query)
        for ret in rets:
            comp_name,comp_id,shop_id,shop_group = ret
            if comp_id not in datas.keys():
                datas[comp_id] = {'name':comp_name,'id':comp_id,'groups':[]}
            datas[comp_id]['groups'] =datas[comp_id]['groups'] + [{'id':shop_id,'name':shop_group}]
        return datas
        
    
    def get_comp_name(self,shop_group=None,shop_name=None,device=None,comp_id=None):
        if shop_group!=None:
            query = 'select name from companies where id in (select company_id from groups where id="%s")'%(shop_group)
        elif shop_name!=None:
            query = 'select name from companies where id in (select company_id from groups where name="%s")'%(shop_name)
        elif device!=None:
            query = 'select name from companies where id in (select company_id from devices  where deviceid="%s")'%(device)
        elif comp_id!=None:
            query = 'select name from companies where id ="%s"'%comp_id
        else:
            return None
        ret = myRetail.SelectOne(query)
        if ret:
            name = ret[0]
            return name
        return None
    def get_group_id(self,tab='groups',name=None,comp_id=None):
        query = 'select id from %s where name="%s" and company_id="%s"'%(tab,name,comp_id)
        ret = myRetail.SelectOne(query)
        if ret:
            id = ret[0]
            return id
        return None
    
    def get_comp_group(self,tab='groups',comp_id=None,shop_group=None):
        if comp_id!=None:
            query = 'select id from %s where company_id="%s" and type=0'%(tab,comp_id)
        elif shop_group!=None:
            query = 'select id from %s where company_id in (select company_id from %s where id ="%s") and type=0'%(tab,tab,shop_group)
        
        ret = myRetail.SelectOne(query)
        if ret==None:
            logging.error("Company Id:%s Not Exists In Tab %s."%(comp_id,tab))
            return comp_id
        else:
            comp_group = ret[0]
            logging.info("Company Id:%s or shop_group:%s id Exists In Tab %s,Company Group is %s."%(comp_id,shop_group,tab,comp_group))
        return comp_group
        
    def get_comp_id(self,tab='groups',comp_group=None,comp_name=None):
        comp_id = None
        if comp_group==None  and comp_name==None:
            logging.error("Company Group Or Company Name Must Be Required.")
            return comp_id
        if comp_group!=None:
            query = 'select company_id from %s where id="%s"'%(tab,comp_group)
        elif comp_name!=None:
            query = 'select id from companies where name="%s"'%(comp_name)
        logging.debug(query)
        ret = myRetail.SelectOne(query)
        if ret==None:
            logging.error("Company Group:%s %s Not Exists In Tab %s."%(comp_group,comp_name,tab))
            return comp_id
        else:
            comp_id = ret[0]
            logging.info("Company Group:%s %s id Exists In Tab %s,Company Id is %s."%(comp_group,comp_name,tab,comp_id))
        return comp_id
    
    def get_groups_by_company_type(self,company_type=''):
        query = 'select g.id from groups g,companies c where c.company_type="%s" and c.id=g.company_id'%company_type
        logging.debug(query)
        rets = myRetail.SelectAll(query)
        if not rets:
            logging.warn('Cannot Find Any row where company_typ=%s In Tables.'%(company_type))
            return []
        groups = []
        for ret in rets:
            groups.append(int(ret[0]))
        logging.info('Find %s Groups Where company_type=%s'%(len(groups),company_type))
        return groups
    def get_groups_by_cate(self,tab='group_cate',first_cate_id=None,first_cate_name=None,second_cate_id=None,second_cate_name=None,comp_id=None):
        query = 'select group_id from %s'%tab
        if first_cate_id!=None:
            query_cate = 'first_cate_id="%s"'%first_cate_id
        elif first_cate_name!=None:
            query_cate = 'first_cate_name="%s"'%first_cate_name
        elif second_cate_id!=None:
            query_cate = 'second_cate_id="%s"'%second_cate_id
        elif second_cate_name!=None:
            query_cate = 'second_cate_name="%s"'%second_cate_name
        else:
            query_cate=None
            
        if comp_id!=None:
            query_comp = 'company_id="%s"'%comp_id
        else:
            query_comp = None
            
        if query_cate!=None and query_comp!=None:
            query = query + ' where '+query_cate+ ' and '+query_comp
        elif query_cate!=None:
            query = query + ' where '+query_cate
        elif  query_comp!=None:
            query = query + ' where '+query_comp
        else:
            logging.error('Cate Or Company Id is required.')
            return []
        rets = myRetail.SelectAll(query)
        groups = []
        msg = ''
        if query_comp!=None:msg+=query_comp
        if query_cate!=None:msg=msg+','+query_cate
        if not rets:

            logging.warning('Not Find Records In Tables , where %s'%(msg))
            return groups
        for ret in rets:
            group = int(ret[0])
            groups.append(group)
        logging.info('Find %s Groups From Tabs ,Where %s'%(len(groups),msg))
        return groups
    
    def get_groups_by_region(self,tab='groups',prov=None,city=None,dist=None,comp_group=None,comp_id=None):
        if prov==None and city==None and dist==None:
            return []
        query = 'select id from %s where type="4"'%tab
        if prov!=None:
            query = query + ' and province="%s"'%prov
        if city!=None:
            query = query + ' and city="%s"'%city
        if dist!=None:
            query = query + ' and distribution="%s"'%dist
        if comp_group!=None and comp_id==None:
            comp_id = self.get_comp_id(comp_group=comp_group)
            if comp_id==None:
                sys.exit()
            query = query + ' and company_id="%s"'%(comp_id)
        elif comp_id!=None:
            query = query + ' and company_id="%s"'%(comp_id)
        rets = myRetail.SelectAll(query)
        groups = []
        if not rets:
            logging.warning('Not Find Any Groups From Tab %s'%(tab))
            return groups
        for ret in rets:
            groups.append(int(ret[0])) 
        logging.info('Find %s Groups From Tab %s'%(len(groups),tab))
        return groups
    
    def get_groups_by_comp(self,tab='groups',comp_id=None,comp_group=None):
        groups = []
        if comp_id==None and comp_group==None:return groups
        elif comp_id==None:
            query = 'select company_id from %s where id="%s"'%(tab,comp_group)
            comp_ret = myRetail.SelectOne(query)
            if not comp_ret:return groups
            comp = comp_ret[0]
        else:
            comp = comp_id
        query = 'select id from %s where company_id="%s" and type="4"'%(tab,comp)
        print query
        rets = myRetail.SelectAll(query)
        for ret in rets:
            groups.append(int(ret[0]))
        return groups
    
    
    
    def get_group_devices(self,comp_id=None,group=None):
        query = 'select d.deviceid,gd.group_id from devices d,group_device gd where  d.id=gd.device_id'
        if comp_id==None and group==None:
            pass
        elif comp_id==None:
            comp_id = self.get_comp_id(comp_group=group)
        else:
            groups = self.get_groups_by_comp(comp_id=comp_id)
    
    def get_groups_info_by_comp(self,tab='groups',comp_id=None,groups=[],comp_group=None):
        if len(groups)==0:
            groups = self.get_groups_by_comp(comp_id=comp_id, comp_group=comp_group)
        datas = self.get_groups_info(groups= groups)
        return datas

    def get_all_groups_info(self,tab='groups'):
        datas = {}
        query = 'select g.id,g.name,g.serial_number,g.province,g.city,g.distribution,c.name,c.company_type from groups g,companies c where c.id=g.company_id and g.type=4'
        rets = myRetail.SelectAll(query)
        if rets==None:rets = []
        for id,name,serial_number,province,city,distribution,comp_name,comp_type in rets:
            if province==None:province = '-'
            if city == None : city='-'
            if distribution ==None :distribution = '-'
            if comp_type == None:comp_type = '-'
            if serial_number==None:serial_number='-'
            name = name.encode("utf-8")
            province = province.encode("utf-8")
            city = city.encode("utf-8")
            distribution = distribution.encode("utf-8")
            comp_name = comp_name.encode('utf-8')
            comp_type = comp_type.encode('utf-8')
            datas[int(id)] = {'name':name,'serial_number':serial_number,'province':province,'city':city,'dist':distribution,'comp_name':comp_name,'comp_type':comp_type}
        return datas

    def get_groups_info(self,tab='groups',groups=[]):
        datas = {}
        if groups==None or len(groups)==0:
            return datas
        groups_str = self.conv_tuple_to_str(groups)
        query = 'select id,name,serial_number,province,city,distribution,address from groups where id in (%s)'%groups_str
        rets = myRetail.SelectAll(query)
        if rets==None:rets = []
        for id,name,serial_number,province,city,distribution,address in rets:
            if serial_number==None:serial_number='-'
            if distribution == None:distribution = '-'
            if address == None : address='-'
            if name==None:name = '-'
            if province == None: province = '-'
            if city == None : city = '-'
            name = name.encode("utf-8")
            province = province.encode("utf-8")
            city = city.encode("utf-8")
            distribution = distribution.encode("utf-8")
            address = address.encode("utf-8")
            datas[int(id)] = {'name':name,'serial_number':serial_number,'province':province,'city':city,'dist':distribution,'address':address}
        return datas
    
    def get_devices_by_group(self,groups=[]):
        datas = {}
        query = 'select gd.group_id,d.deviceid from devices d,group_device gd where  gd.device_id=d.id'
        if groups!=[]:
            groups_str = self.conv_tuple_to_str(groups)
            query = query + ' and gd.group_id in (%s)'%groups_str
        logging.debug(query)
        rets = myRetail.SelectAll(query)
        if rets==None:rets=[]
        for group , device in rets:
            if int(group) not in datas.keys():
                datas[int(group)] = []
            datas[int(group)] = datas[int(group)]+[device]
        return datas
    
    def get_group_name(self,group=None,device=None):
        if device!=None:
            query = 'select g.name from groups g ,devices d,group_device gd where g.id=gd.group_id and d.id=gd.device_id and d.deviceid="%s"'%device
        elif group!=None:
            query = 'select name from groups where id="%s"'%group
        else:
            logging.error('You Must Input  At Least One From Group And Device.')
            return None
        logging.debug(query)
        ret = myRetail.SelectOne(query)
        if ret==None:return None
        return ret[0]
    
    def get_devices_by_groups(self,groups=[]):
        devices = []
        query = 'select deviceid from devices d,group_device gd where d.id=gd.device_id'
        if len(groups)!=0:
            groups_str = self.conv_tuple_to_str(groups)
            query = query + ' and gd.group_id in (%s)'%groups_str
        rets = myRetail.SelectAll(query)
        if rets==None:return devices
        for ret in rets:
            devices.append(ret[0])
        return devices
    
    
    def get_group_type(self,group):
        query = 'select type from groups where id="%s"'%group
        logging.debug(query)
        ret = myRetail.SelectOne(query)
        if ret==None:return None
        return int(ret[0])
    
    
    
    
    def get_shop_groups_by_group(self,groups=[]):
        newGroups = []
        for group in groups:
            groupType = int(self.get_group_type(group))
            logging.error('%s %s'%(group,groupType))
            if groupType == 0 :
                shopGroups = self.get_groups_by_comp( comp_group=group)
            elif groupType == 1:
                shopGroups = self.get_shop_groups_by_prov(prov_group=group)
            elif groupType == 2:
                shopGroups = self.get_shop_groups_by_city(city_group=group)
            elif groupType == 4:
                shopGroups = [group]
            newGroups+=shopGroups
        return newGroups
            
    def get_groups_by_parent(self,parent):
        query = 'select id from groups where parent_id="%s"'%parent
        rets = myRetail.SelectAll(query)
        if rets==None:rets=[]
        groups = []
        for ret in rets:
            groups.append(int(ret[0]))
        return groups
    
    def get_shop_groups_by_city(self,city_group):
        dist_groups = self.get_groups_by_parent(city_group)
        groups = []
        for dist_group in dist_groups:
            shop_groups = self.get_groups_by_parent(dist_group)
            groups = groups + shop_groups
        return groups
    
    def get_shop_groups_by_prov(self,prov_group):
        city_groups = self.get_groups_by_parent(prov_group)
        groups = []
        for city_group in city_groups:
            shop_groups = self.get_shop_groups_by_city(city_group)
            groups=groups+shop_groups
        return groups
    def get_shop_groups(self,group=None):
        if group==None:
            logging.error('You Must Enter A Group.')
            return []
        query = 'select type from groups where id="%s"'%group
        ret = myRetail.SelectOne(query)
        if ret==None:return []
        gtype = int(ret[0])
        if gtype == 4:return [group]
        elif gtype == 5:return self.get_groups_by_parent(group)
        elif gtype == 2:
            return self.get_shop_groups_by_city(group)
        elif gtype == 1:
            return self.get_shop_groups_by_prov(group)
        elif gtype == 0:
            return self.get_groups_by_comp( comp_group=group)
        else:
            logging.error('The Type(%s) Can Not Analyze'%gtype)
            return []
        
        
    def get_devices_comp_info(self,devices=[]):
        datas = {}
        query = 'select c.id,c.name,g.id,g.name,g.serial_number,g.province,g.city,g.distribution,d.deviceid,d.version from companies c,groups g,devices d,group_device gd where gd.group_id=g.id and g.company_id=c.id and gd.device_id=d.id'
        if devices!=[]:
            devices_str = self.conv_tuple_to_str(devices)
            query = query + ' and d.deviceid in (%s)'%devices_str
        rets = myRetail.SelectAll(query)
        if rets == None:rets = []
        for comp_id,comp_name,group_id,group_name,serial_number,prov,city,dist,device,version in rets:
            datas[device] = {'comp_id':comp_id,
                             'comp_name':comp_name,
                             'group_id':group_id,
                             'group_name':group_name,
                             'prov':prov,
                             'city':city,
                             'dist':dist,
                             'group_serial_number':serial_number,
                             'version':version,
                             'device':device}
        for device in devices:
            if device not in datas.keys():
                datas[device] = {'comp_id':'-',
                             'comp_name':'-',
                             'group_id':'-',
                             'group_name':'-',
                             'prov':'-',
                             'city':'-',
                             'dist':'-',
                             'group_serial_number':'-',
                             'version':'-',
                             'device':device
                                 }
        return datas
        
    def get_shop_brand_by_field(self,tab='group_brand',field='group_id',value=None):
        brand_names = []
        if value==None:return brand_names
        
        query = 'select brand_name from %s where %s="%s"'%(tab,field,value)
        logging.debug(query)
        
        rets = myRetail.SelectAll(query)
        if rets==None:
            return brand_names
        for ret in rets:
            brand_names.append(ret[0])
        return brand_names
    
    
        
    def get_shop_brand(self,comp_id=None,shop_group=None):
        brand_names = self.get_shop_brand_by_field(field='group_id',value=shop_group)
        if len(brand_names)==0:
            brand_names = self.get_shop_brand_by_field(field='company_id',value=comp_id)
        return brand_names
        
    
myRetailDatas = RetailDatas()   
        
if __name__ == '__main__':
    pass